﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Xml;

namespace Dal
{
    public class DBHelper
    {
        //获取连接字符串
        private static string connectionString = IniFile.ReadIniValue("Settings", "UserDataBaseSetting", "", "Config.ini");
        private static string connectionStringTemp = IniFile.ReadIniValue("Settings", "GPSDataBaseSetting", "", "Config.ini");
        /// <summary>
        /// 执行查询语句
        /// </summary>
        public static SqlDataReader ExecuteReader(string sql, CommandType comType, SqlParameter[] sqlpar)
        {
            SqlConnection sqlConnection = new SqlConnection(connectionString);
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            try
            {
                sqlConnection.Open();
                sqlCommand.CommandType = comType;
                if (sqlpar != null)
                {
                    sqlCommand.Parameters.AddRange(sqlpar);
                }
                SqlDataReader sdr = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
                return sdr;
            }
            catch(Exception ex)
            {
                LogManager.WriteLog(LogFile.Dal_Error,ex.Message);
                return null;
            }
        }
        /// <summary>
        /// 从远程数据库执行查询语句
        /// </summary>
        public static SqlDataReader ExecuteReader(string sql, CommandType comType, SqlParameter[] sqlpar, bool temp)
        {
            if (temp)
            {
                SqlConnection sqlConnection = new SqlConnection(connectionStringTemp);
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                try
                {
                    sqlConnection.Open();
                    sqlCommand.CommandType = comType;
                    if (sqlpar != null)
                    {
                        sqlCommand.Parameters.AddRange(sqlpar);
                    }
                    SqlDataReader sdr = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
                    return sdr;
                }
                catch(Exception ex)
                {
                    LogManager.WriteLog(LogFile.Dal_Error, ex.Message);
                    return null;
                }
            }
            else
            {
                return null;
            }

        }
        /// <summary>
        /// 执行曾、删、改语句
        /// </summary>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, CommandType comType, SqlParameter[] sqlpar)
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                try
                {
                    sqlConnection.Open();
                    sqlCommand.CommandType = comType;
                    if (sqlpar != null)
                    {
                        sqlCommand.Parameters.AddRange(sqlpar);
                    }
                    return sqlCommand.ExecuteNonQuery();
                }
                catch(Exception ex)
                {
                    LogManager.WriteLog(LogFile.Dal_Error, ex.Message);
                    return 0;
                }
            }
        }
        /// <summary>
        /// Gps执行曾、删、改语句
        /// </summary>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, CommandType comType, SqlParameter[] sqlpar, bool temp)
        {
            if (temp)
            {
                using (SqlConnection sqlConnection = new SqlConnection(connectionStringTemp))
                {
                    SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                    try
                    {
                        sqlConnection.Open();
                        sqlCommand.CommandType = comType;
                        if (sqlpar != null)
                        {
                            sqlCommand.Parameters.AddRange(sqlpar);
                        }
                        return sqlCommand.ExecuteNonQuery();
                        
                    }
                    catch(Exception ex)
                    {
                        LogManager.WriteLog(LogFile.Dal_Error, ex.Message);
                        return 0;
                    }
                }
            }
            else
            {
                return 0;
            }
        }
        /// <summary>
        /// 返回首行首列
        /// </summary>
        /// <returns></returns>
        public static int ExecuteScalar(string sql, CommandType comType, SqlParameter[] sqlpar)
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                try
                {
                    sqlConnection.Open();
                    sqlCommand.CommandType = comType;
                    if (sqlpar != null)
                    {
                        sqlCommand.Parameters.AddRange(sqlpar);
                    }
                    return Convert.ToInt32(sqlCommand.ExecuteScalar());
                }
                catch(Exception ex)
                {
                    LogManager.WriteLog(LogFile.Dal_Error, ex.Message);
                    return 0;
                }
            }
        }
        /// <summary>
        /// 数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="comType"></param>
        /// <param name="sqlpar"></param>
        /// <returns></returns>
        public static DataTable DataAdapter(string sql, CommandType comType, SqlParameter[] sqlpar)
        {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                    sqlCommand.CommandType = comType;
                    if (sqlpar != null)
                    {
                        sqlCommand.Parameters.AddRange(sqlpar);
                    }
                    DataSet ds = new DataSet();
                    SqlDataAdapter sda = new SqlDataAdapter(sqlCommand);
                    sda.Fill(ds);
                    return ds.Tables[0];
                }
                catch(Exception ex)
                {
                    LogManager.WriteLog(LogFile.Dal_Error, ex.Message);
                    return null;
                }
            }
        }
        /// <summary>
        /// 远程数据库返回数据集
        /// </summary>
        public static DataTable DataAdapter(string sql, CommandType comType, SqlParameter[] sqlpar, bool temp)
        {
            if (temp)
            {
                using (SqlConnection sqlConnection = new SqlConnection(connectionStringTemp))
                {
                    try
                    {
                        sqlConnection.Open();
                        SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
                        sqlCommand.CommandTimeout = 120;
                        sqlCommand.CommandType = comType;
                        if (sqlpar != null)
                        {
                            sqlCommand.Parameters.AddRange(sqlpar);
                        }
                        DataSet ds = new DataSet();
                        SqlDataAdapter sda = new SqlDataAdapter(sqlCommand);
                        sda.Fill(ds);
                        return ds.Tables[0];
                    }
                    catch(Exception ex)
                    {
                        LogManager.WriteLog(LogFile.Dal_Error, ex.Message);
                        return null;
                    }
                }
            }
            else
            {
                return null;
            }

        }
    }
}
